-- @owner: @haomeng802
-- @date: 2023/1/4
-- @testpoint: connect by子句,插入较大量数据(>50w)，查询结果受max_recursive_times参数控制,合理报错


--step1:创建普通表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_025 cascade;
create table t_connectby_025(id int, fatherid int);

insert into t_connectby_025
select a,case when b < 0 then 0 else b end b
from (select generate_series(1, 1000) a,generate_series(-2, 500) b);

select count(*) from t_connectby_025;

--step2:查看部分数据;expect:默认参数下，查询失败，超出最大迭代次数
show max_recursive_times;
select * from t_connectby_025 start with id=1 connect by nocycle prior id = fatherid limit 10;

--step3:清理环境;expect:成功
drop table t_connectby_025 cascade;
